﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Finance.TodoSoft;

namespace TodoSoft.Data
{
	public abstract class SqlCommandProcessorBase : ISqlCommandProcessor
	{
		protected DbConnection Connection;
		private readonly bool _closeCnnectionOnDispose;
		private IDictionary<Type, string> _typeNames;

		protected string AlterTableDropColumnQueryTemplate = "ALTER TABLE {0} DROP COLUMN \"{1}\";";
		protected string AlterTableAddColumnQueryTemplate = "ALTER TABLE {0} ADD COLUMN \"{1}\" {2};";
		protected string AlterTableAlterColumnTypeQyeryTemplate = "ALTER TABLE {0} ALTER COLUMN \"{1}\" TYPE {2}";
		protected string CreateTableWithIdQueryTemplate =
			"CREATE TABLE {0} ({1} uniqueidentifier ROWGUIDCOL NOT NULL) ";

		protected DbProviderFactory DbProvider;

		protected SqlCommandProcessorBase(DbConnection connection) :this(connection, false) { }

		protected SqlCommandProcessorBase(DbConnection connection, bool closeCnnectionOnDispose)
		{
			Connection = connection;
			_closeCnnectionOnDispose = closeCnnectionOnDispose;
		}

		#region IDisposable implementation
		public void Dispose()
		{
			if (_closeCnnectionOnDispose && Connection != null)
			{
				Connection.Close();
				Connection = null;
			}
		}
		#endregion

		private DbTransaction _transaction;
		public void TransactionBegin()
		{
			if (_transaction != null)
				throw new Exception("Transaction already open.");

			_transaction = Connection.BeginTransaction();
		}

		public void TransactionCommit()
		{
			if (_transaction == null)
				throw new Exception("There is no opened transaction");

			_transaction.Commit();
			_transaction = null;
		}

		public void TransactionRollback()
		{
			if (_transaction == null)
				throw new Exception("There is no opened transaction");

			_transaction.Rollback();
			_transaction = null;
		}

		public abstract bool IsTableExist(string tableName);

		protected DbCommand CreateCommand(string sqlText, params object[] parameters)
		{
			var command = DbProvider.CreateCommand();
			command.CommandText = sqlText;
			command.Connection = Connection;

			if(_transaction != null)
				command.Transaction = _transaction;
            
			if(parameters != null)
				AddParameters(command, parameters);

			return command;
		}

		protected DbCommand CreateCommand(string sqlText, Dictionary<string, object> parameters)
		{
			var command = DbProvider.CreateCommand();
			command.CommandText = sqlText;
			command.Connection = Connection;

			if (_transaction != null)
				command.Transaction = _transaction;

			foreach (var parameter in parameters)
			{
				var dbParameter = CreateParameter(command, parameter.Value, parameter.Key);
				command.Parameters.Add(dbParameter);
			}

			return command;
		}

		public void ExecuteNonQuery(string sqlText, params object[] args)
		{
			using (var sqlCommand = CreateCommand(sqlText, args))
			{
				sqlCommand.ExecuteNonQuery();
			}
		}

		public IDataReader ExecuteReader(string sqlText, params object[] parameters)
		{
			using (var sqlCommand = CreateCommand(sqlText, parameters))
			{
				return sqlCommand.ExecuteReader();
			}
		}

		public IDataReader ExecuteReader(string queryText, Dictionary<string, object> parameters)
		{
			using (var sqlCommand = CreateCommand(queryText, parameters))
			{
				return sqlCommand.ExecuteReader();
			}
		}

		public void AlterTableAddIndex(string tableName, string fieldName)
		{
			ExecuteNonQuery("create index " + fieldName+"_idx on " + tableName+" (" + fieldName+")");
		}

		public void AlterTableDropIndex(string tableName, string fieldName)
		{
			ExecuteNonQuery("drop index " + tableName +"." + fieldName+"_idx");
		}

		public void AlterTableAddPrimaryKey(string tableName, string fieldName)
		{
			ExecuteNonQuery("ALTER TABLE " + tableName + " ALTER COLUMN " + fieldName + " uniqueidentifier NOT NULL");
			ExecuteNonQuery("alter table " + tableName + " add primary key (" + fieldName+")");
		}

		public void DropTable(string tableName)
		{
			ExecuteNonQuery("drop table " + tableName);
		}

		

		protected abstract IDbDataParameter CreateParameter(IDbCommand sqlCommand, object value, string name);

		protected void AddParameters(IDbCommand sqlCommand, object[] args)
		{
			for (int index = 0; index < args.Length; index++)
			{
				var arg = args[index];
				var parameter = CreateParameter(sqlCommand, arg, "p" + index);
				sqlCommand.Parameters.Add(parameter);
			}
		}

		protected abstract Dictionary<Type, string> CreateDbTypesMap();


		public void CreateTable(string tableName)
		{
			CreateTable(tableName, tableName+"id");
		}

		/// <summary>
		/// Creates table with id field.
		/// </summary>
		/// <param name="tableName">Name of table to create</param>
		/// <param name="idFieldName">Id column name</param>
		public void CreateTable(string tableName, string idFieldName)
		{
			ExecuteNonQuery(String.Format(CreateTableWithIdQueryTemplate, tableName, idFieldName));
		}

		public void AlterTableAddColumn(string tableName, string columnName, Type type)
		{
			var typeName = ConvertToDbTypeName(type);

			ExecuteNonQuery(String.Format(
				AlterTableAddColumnQueryTemplate,
				tableName,
				columnName,
				typeName)
				);
		}

		public void AlterTableDropColumn(string tableName, string columnName)
		{
			ExecuteNonQuery(String.Format(
				AlterTableDropColumnQueryTemplate,
				tableName,
				columnName)
				);
		}

		public void AlterTableAddColumn(string tableName, string columnName, string dbTypeName)
		{
			ExecuteNonQuery(String.Format(
				AlterTableAddColumnQueryTemplate,
				tableName,
				columnName,
				dbTypeName));
		}

		public void AlterTableChangeColumnType(string tableName, string name, Type type, int size)
		{
			var typeName = ConvertToDbTypeName(type);
			var dbType = typeName + "(" + size + ")";

			ExecuteNonQuery(String.Format(
				AlterTableAlterColumnTypeQyeryTemplate,
				tableName,
				name,
				dbType));
		}

		public void Insert(string documentTypeTable, string[] fields, object[] values)
		{
			var fieldsString = fields.Aggregate((a, b) => a + ", " + b);

			var parameters = "?".Repeat(fields.Length).Aggregate((a, b) => a + ", " + b);
			
			ExecuteNonQuery(string.Format("insert into {0} ({1}) values ({2})",
				documentTypeTable,
				fieldsString,
				parameters), values);
			
		}

		public void AlterTableAddColumn(string tableName, string columnName, Type type, int size)
		{
			var typeName = ConvertToDbTypeName(type);
			var dbType = typeName + "(" + size + ")";

			ExecuteNonQuery(String.Format(
				AlterTableAddColumnQueryTemplate,
				tableName,
				columnName,
				dbType));
		}

		public object ExecuteScalar(string query, params object[] args)
		{
			using (var command = CreateCommand(query, args))
			{
				return command.ExecuteScalar();
			}
		}

		IDictionary<Type, string> DbTypesMap
		{
			get { return _typeNames ?? (_typeNames = CreateDbTypesMap()); }
		}

		protected string ConvertToDbTypeName(Type type)
		{
			if (!DbTypesMap.ContainsKey(type))
				throw new Exception("Unknown type " + type.Name);

			return DbTypesMap[type];
		}
	}
}